package co.gov.medellin.ssti.sub.model.dao.impl;

import java.io.UnsupportedEncodingException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedList;
import java.util.List;

import co.gov.medellin.ssti.sub.common.excepcion.SUBException;
import co.gov.medellin.ssti.sub.model.dao.ICargueDAO;
import co.gov.medellin.ssti.sub.model.dao.exception.DAOException;
import co.gov.medellin.ssti.sub.model.vo.CargueVO;
import co.gov.medellin.ssti.sub.model.vo.RegNoProcesadosVO;

public class CargueDAOImpl extends BaseDAOImpl implements ICargueDAO {
	private static final String FIND_ARCHIVO_CARGUE = " SELECT lote.nombre nombre, lote.codigo lote, lote.archivo archivo, "
			+ " DECODE(lote.tipo_terminacion,'N','Normal','E','Error') terminacion, "
			+ " vfl_codigo.nombre valor, lote.usuario_crea usuario, lote.fecha_crea fechaCrea, "
			+ " beneficios.fcs_ben_getArea(lote.usuario_crea) dependencia, paoyer.fcs_getTotal(lote.codigo) total, "
			+ " paoyer.fcs_getCargados(lote.codigo) cargados "
			+ " FROM paoyer.TCS_LOTE_CARGUE lote, paoyer.TCS_VALOR_FLEXIBLE VFL_CODIGO "
			+ " WHERE LOTE.VFL_CODIGO = VFL_CODIGO.CODIGO ";

	private static final String TOTAL_REGISTROS = " SELECT count(codigo) total FROM paoyer.TCS_API_CARGUE WHERE LCA_CODIGO = ? ";
	private static final String TOTAL_REGISTROS_CARGADOS = " SELECT count(codigo) totalCargados FROM paoyer.TCS_API_CARGUE WHERE LCA_CODIGO = ? "
			+ " AND estado = 'C'";

	private static final String SELECT_NOMBRE_LOTE_CARGUE_BY_NOMBRE = " SELECT NOMBRE FROM paoyer.TCS_LOTE_CARGUE WHERE NOMBRE = UPPER(?) ";
	private static final String INSERT_LOTE_CARGUE = " INSERT INTO paoyer.TCS_LOTE_CARGUE (CODIGO,NOMBRE,VFL_CODIGO,ARCHIVO, "
			+ " USUARIO_CREA,TERMINAL_CREA,FECHA_CREA,USUARIO_MODIFICA,TERMINAL_MODIFICA,FECHA_MODIFICA) "
			+ " VALUES(paoyer.SCS_LOTE_CARGUE.NEXTVAL,UPPER(?),?,UPPER(?),?,?,TO_DATE(?,'dd/mm/rrrr'),?,?,TO_DATE(?,'dd/mm/rrrr')) ";
	private static final String SELECT_MAX_CODIGO_LOTE_CARGUE = "SELECT MAX(codigo) codigo FROM paoyer.TCS_LOTE_CARGUE ";
	private static final String SELECT_CANTIDAD_LOTE_CARGUE = "SELECT COUNT(1) cantidad FROM paoyer.TCS_LOTE_CARGUE WHERE ('ADMINISTRADOR GENERAL' = ? OR 'SUPER USUARIO' = ? OR usuario_crea = ? ) AND codigo = ? ";
	private static final String UPDATE_LOTE_CARGUE_ARCHIVO_EN_WEB = "UPDATE paoyer.TCS_LOTE_CARGUE SET ARCHIVO_EN_WEB = ? WHERE codigo = ? ";
	private static final String DELETE_LOTE_CARGUE_BY_CODIGO = "DELETE FROM paoyer.tcs_lote_cargue WHERE codigo = ? ";
	private static final String DELETE_LOTE_CARGUE_BY_LCA_CODIGO = "DELETE FROM paoyer.tcs_lote_cargue WHERE lca_codigo = ? ";
	private static final String DELETE_API_CARGUE_BY_LCA_CODIGO = "delete from paoyer.tcs_api_cargue where lca_codigo = ? ";
	private static final String CALL_P_POBLA_DATOS_DE_CLOB = "{call paoyer.PQCS_CARGUE.p_pobla_datos_de_clob(?)}";
	private static final String SELECT_ALL_API_CARGUE_BY_LCA_CODIGO = " SELECT * FROM paoyer.TCS_API_CARGUE WHERE lca_codigo = ? ";
	private static final String SELECT_COUNT_CODIGO_API_CARGUE_BY_LCA_CODIGO = "SELECT count(codigo) FROM paoyer.TCS_API_CARGUE WHERE LCA_CODIGO = ? ";
	private static final String SELECT_CODIGO_API_CARGUE_BY_LCA_CODIGO = "SELECT codigo FROM paoyer.TCS_API_CARGUE WHERE lca_codigo = ? ";
	private static final String CALL_PCS_CAR_BEN_PER = "{call Pcs_Car_Ben_Per(?,?,?)}";
	private static final String CALL_PCS_CAR_BEN_OTOR = "{call Pcs_Car_Ben_Otor(?,?,?,?)}";
	private static final String CALL_P_BORRAR_DATOS = "{call paoyer.PQCS_CARGUE.p_borrar_datos(?)}";

	public CargueDAOImpl(Connection con) {
		super(con);
	}

	public Collection<CargueVO> findArchivoCargueByCriteria(CargueVO criteria)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		ArrayList<CargueVO> archivos = new ArrayList<CargueVO>();
		StringBuffer query = new StringBuffer(FIND_ARCHIVO_CARGUE);
		List<Object> parametros = new ArrayList<Object>();

		if (criteria.getNombre() != null
				&& !criteria.getNombre().equalsIgnoreCase("")) {
			query.append(" AND UPPER(lote.nombre) LIKE UPPER(?) ");
			parametros.add("%" + criteria.getNombre() + "%");
		}
		if (criteria.getCode() != null
				&& !criteria.getCode().equalsIgnoreCase("")) {
			query.append(" AND lote.codigo = ? ");
			parametros.add(criteria.getCode());
		}
		if (criteria.getFechaFinal() != null
				&& !criteria.getFechaFinal().equals("")
				&& criteria.getFechaInicial() != null
				&& !criteria.getFechaInicial().equals("")) {
			query.append(" AND lote.fecha_crea BETWEEN TO_DATE(?,'dd/mm/yyyy') AND TO_DATE(?,'dd/mm/yyyy')");
			parametros.add(criteria.getFechaInicial());
			parametros.add(criteria.getFechaFinal());
		}
		query.append(" ORDER BY lote.codigo ");

		try {
			pstmt = conn.prepareStatement(query.toString());
			setQueryParameters(pstmt, parametros);
			rst = pstmt.executeQuery();
			CargueVO cargue = null;

			while (rst.next()) {
				cargue = new CargueVO();
				cargue.setNombre(rst.getString("nombre"));
				cargue.setCode(rst.getString("lote"));
				cargue.setArchivo(rst.getString("archivo"));
				cargue.setTipo_terminacion(rst.getString("terminacion"));
				cargue.setVflcodigo(rst.getString("valor"));
				cargue.setFechaCreacion(rst.getString("fechaCrea").substring(0,
						10));
				cargue.setUsuarioCreador(rst.getString("usuario"));
				cargue.setDependencia(rst.getString("dependencia"));
				cargue.setTotalRegistros(rst.getString("total"));
				cargue.setTotalCargados(rst.getString("cargados"));
				// String totales[] = getTotalRegistros(cargue.getCode());
				// cargue.setTotalRegistros(totales[0]);
				// cargue.setTotalCargados(totales[1]);
				archivos.add(cargue);
				cargue = null;
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return archivos;
	}

	public String[] findTotalRegistrosByCodigoLote(String codeLote)
			throws DAOException {
		PreparedStatement pstmt = null;
		PreparedStatement pstmt2 = null;
		ResultSet rst = null;
		ResultSet rs = null;
		String retorno[] = new String[2];
		try {
			pstmt = conn.prepareStatement(TOTAL_REGISTROS);
			pstmt.setString(1, codeLote);
			rst = pstmt.executeQuery();
			if (rst.next()) {
				retorno[0] = rst.getString("total");
				try {
					pstmt2 = conn.prepareStatement(TOTAL_REGISTROS_CARGADOS);
					pstmt2.setString(1, codeLote);
					rs = pstmt2.executeQuery();
					if (rs.next()) {
						retorno[1] = rs.getString("totalCargados");
					} else {
						retorno[1] = "0";
					}
				} finally {
					close(rs, pstmt2);
				}
			} else {
				retorno[0] = "0";
				retorno[1] = "0";
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return retorno;
	}

	public String[] ingresarCargue(CargueVO criteria, String user,
			String terminal, String fecha) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		CargueVO cargue = null;
		String retorno[] = new String[2];
		retorno[0] = "false";

		try {
			pstmt = conn.prepareStatement(SELECT_NOMBRE_LOTE_CARGUE_BY_NOMBRE);
			pstmt.setString(1, criteria.getNombre().trim());
			rst = pstmt.executeQuery();

			while (rst.next()) {
				cargue = new CargueVO();
				cargue.setNombre(rst.getString("NOMBRE"));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}

		if (cargue != null) {
			retorno[0] = "false";
			return retorno;

		} else {
			int numRows = 0;
			try {
				pstmt = conn.prepareStatement(INSERT_LOTE_CARGUE);
				pstmt.setString(1, criteria.getNombre().trim());
				pstmt.setString(2, criteria.getVflcodigo());
				pstmt.setString(3, criteria.getArchivo());
				pstmt.setString(4, user);
				pstmt.setString(5, terminal);
				pstmt.setString(6, fecha);
				pstmt.setString(7, user);
				pstmt.setString(8, terminal);
				pstmt.setString(9, fecha);
				numRows = pstmt.executeUpdate();
			} catch (SQLException e) {
				throw new DAOException(e);
			} finally {
				close(pstmt);
			}
			if (numRows > 0) {
				try {
					pstmt = conn
							.prepareStatement(SELECT_MAX_CODIGO_LOTE_CARGUE);
					rst = pstmt.executeQuery();
					if (rst.next()) {
						retorno[0] = "true";
						retorno[1] = rst.getString("codigo");
						return retorno;
					} else {
						retorno[0] = "false";
						return retorno;
					}
				} catch (SQLException e) {
					throw new DAOException(e);
				} finally {
					close(rst, pstmt);
				}
			} else {
				retorno[0] = "false";
				return retorno;
			}

		}
	}

	public boolean validateDelete(Long codeUser, String nameUser,
			String groupUserName, String codeLote) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		try {
			if ((Object) codeUser == null) {
				return false;
			}
			pstmt = conn.prepareStatement(SELECT_CANTIDAD_LOTE_CARGUE);
			pstmt.setString(1, groupUserName);
			pstmt.setString(2, groupUserName);
			pstmt.setString(3, nameUser);
			pstmt.setString(4, codeLote);

			rst = pstmt.executeQuery();
			rst.next();
			if (rst.getInt("cantidad") > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
	}

	public int uploadFile(int codeLoteCargue, byte[] fileData)
			throws DAOException, UnsupportedEncodingException {
		PreparedStatement pstmt = null;

		int moveac = 0;
		int a = 0;
		try {
			pstmt = conn.prepareStatement(UPDATE_LOTE_CARGUE_ARCHIVO_EN_WEB);
			pstmt.setString(1, new String(fileData, "ISO-8859-1"));
			pstmt.setInt(2, codeLoteCargue);

			a = pstmt.executeUpdate();
			// conn.commit();
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(pstmt);
		}
		moveac = moveToApiCargue(codeLoteCargue);
		if (moveac >= 0) {
			try {
				pstmt = conn.prepareStatement(DELETE_LOTE_CARGUE_BY_CODIGO);
				pstmt.setInt(1, codeLoteCargue);
				a = pstmt.executeUpdate();
			} catch (SQLException e) {
				throw new DAOException(e);
			} finally {
				close(pstmt);
			}

			try {
				pstmt = conn.prepareStatement(DELETE_LOTE_CARGUE_BY_LCA_CODIGO);
				pstmt.setInt(1, codeLoteCargue);
				a = pstmt.executeUpdate();
			} catch (SQLException e) {
				throw new DAOException(e);
			} finally {
				close(pstmt);
			}
		}
		return moveac;

	}

	private int moveToApiCargue(int codeLoteCargue) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		CallableStatement cs = null;

		int numRows = 0;
		try {
			pstmt = conn.prepareStatement(DELETE_API_CARGUE_BY_LCA_CODIGO);
			pstmt.setInt(1, codeLoteCargue);
			numRows = pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(pstmt);
		}
		try {
			cs = conn.prepareCall(CALL_P_POBLA_DATOS_DE_CLOB);
			cs.setInt(1, codeLoteCargue);
			cs.execute();
		} catch (Exception e) {
			if (e.getMessage()
					.startsWith(
							"ORA-20500: Pqcs_Cargue.P_POBLA_DATOS_DE_CLOB-Inserta la linea en TCS_API_CARGUE")) {
				return getBadRecord(codeLoteCargue);
			} else {
				SUBException.logDebugError("Error moveToApiCargue: ", e);
			}
			return 0;
		} finally {
			close(cs);
		}
		try {
			pstmt = conn.prepareStatement(SELECT_ALL_API_CARGUE_BY_LCA_CODIGO);
			pstmt.setInt(1, codeLoteCargue);
			rst = pstmt.executeQuery();
			if (rst.next()) {
				return -1;
			} else {
				return 0;
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
	}

	private int getBadRecord(int codeLote) {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		try {
			pstmt = conn
					.prepareStatement(SELECT_COUNT_CODIGO_API_CARGUE_BY_LCA_CODIGO);
			pstmt.setInt(1, codeLote);
			rst = pstmt.executeQuery();
			if (rst.next()) {
				return rst.getInt(1) + 1;
			} else {
				return 0;
			}
		} catch (Exception e) {
			return 0;
		} finally {
			close(rst, pstmt);
		}
	}

	public boolean moveToCargarDatos(int codeLoteCargue, String tipoCargue,
			String user, String terminal, String usrGroup, String usrCode)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		CallableStatement cs = null;

		try {
			pstmt = conn
					.prepareStatement(SELECT_CODIGO_API_CARGUE_BY_LCA_CODIGO);
			pstmt.setInt(1, codeLoteCargue);
			rst = pstmt.executeQuery();
			if (!rst.next()) {
				return false;
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		try {
			if (!tipoCargue.equalsIgnoreCase("")
					&& tipoCargue.equals("BENEFICIARIOS")) {
				cs = conn.prepareCall(CALL_PCS_CAR_BEN_PER);
			} else if (!tipoCargue.equalsIgnoreCase("")
					&& tipoCargue.equals("BENEFICIOS")) {
				cs = conn.prepareCall(CALL_PCS_CAR_BEN_OTOR);
				cs.setString(4, usrGroup);
			}
			cs.setInt(1, codeLoteCargue);
			cs.setString(2, user);
			cs.setString(3, terminal);
			cs.execute();
			return true;

		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(cs);
		}
	}

	public boolean deleteFile(int codeLoteCargue) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		CallableStatement cs = null;

		try {
			// Llamada a P_BORRAR_DATOS
			cs = conn.prepareCall(CALL_P_BORRAR_DATOS);
			cs.setInt(1, codeLoteCargue);
			cs.execute();
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(cs);
		}

		try {
			// borrar de PAOYER.TCS_LOTE_CARGUE
			pstmt = conn.prepareStatement(DELETE_LOTE_CARGUE_BY_CODIGO);
			pstmt.setInt(1, codeLoteCargue);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(pstmt);
		}
		try {
			pstmt = conn.prepareStatement(SELECT_ALL_API_CARGUE_BY_LCA_CODIGO);
			pstmt.setInt(1, codeLoteCargue);
			rst = pstmt.executeQuery();
			return rst.next() ? false : true;

		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
	}

	public Collection<RegNoProcesadosVO> findRegistrosNoProcesados(String lote,
			String tipo) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		Collection<RegNoProcesadosVO> regs = new LinkedList<RegNoProcesadosVO>();
		String query = "";
		if (tipo != null && !tipo.equalsIgnoreCase("")) {
			if (tipo.equals("BENEFICIARIOS")) {
				query = "SELECT lca_codigo, comentario,c_a1 tipo,c_a2 numero "
						+ "FROM paoyer.tcs_api_cargue api "
						+ "WHERE lca_codigo = ? AND api.ESTADO = 'E'";
			} else if (tipo.equals("BENEFICIOS")) {
				query = "SELECT lca_codigo, comentario,c_a3 tipo,c_a4 numero "
						+ "FROM paoyer.tcs_api_cargue api "
						+ "WHERE lca_codigo = ? AND api.ESTADO = 'E'";
			}
		}
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, lote);

			RegNoProcesadosVO reg = null;
			rst = pstmt.executeQuery();

			while (rst.next()) {
				reg = new RegNoProcesadosVO();
				reg.setCode(rst.getString("lca_codigo"));
				reg.setComentario(rst.getString("comentario"));
				reg.setTipoDocumento(rst.getString("tipo"));
				reg.setNumeroDocumento(rst.getString("numero"));
				regs.add(reg);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return regs;
	}

	public int deleteCargue(String codeLote) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		CallableStatement cs = null;
		String query = "SELECT COUNT(1) CANTIDAD "
				+ " FROM TCS_BEN_BENEFICIO_OTORGADO WHERE CAMPO_N1 = ? ";

		try {
			pstmt = conn.prepareStatement(query);
			rst = pstmt.executeQuery();
			rst.next();
			if (rst.getInt("CANTIDAD") > 0) {
				String strSQL = "{call beneficios.PCS_BEN_DELETE_BOT(?,?)}";
				try {
					cs = conn.prepareCall(strSQL);
					cs.setString(1, codeLote);
					cs.registerOutParameter(2, Types.INTEGER);
					cs.execute();
					return cs.getInt(1);
				} catch (SQLException e) {
					throw new DAOException(e);
				} finally {
					close(cs);
				}
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return 0;
	}
}
